********************************************************************************
*DYNAMIC IMPACTS OF PRICING GROUNDWATER
*Bruno, Jessoe, Hanemann in JAERE
*********************************************************************************
*GROUNDWATER QUALITY DATA POST-PYTHON PROCESSING 
*MERGES CHLORIDE FILES INTO ONE AND CLEANS TO MERGE INTO FULL STATA DATASET
*******************************************************************************
clear all
capture log close
set more off

*SELECT OUTPUT DATE
global outputdate = "20220729"		 

*SET DIRECTORY
cd  "D:\Ellen\Dropbox\Pajaro_AgInnovation" 


/* RECALL
bad_list = ["2001:1", "2001:3",  \
            "2002:1", \
            "2005:1", \
            "2006:1", \
            "2008:1", "2008:3", \
            "2009:2", \
            "2020:1", "2020:3" ]
*/

*QUARTER 1
local myyear 2003 2004 2007 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 
local myquarter 1
foreach x of local myyear {
foreach y of local myquarter {
	import excel "Data\CL_tables\Replicate\CL_to_wells_`x'_Q`y'_$outputdate.xls", sheet("CL_to_wells_`x'_Q`y'_$outputdate") firstrow clear
	destring, replace
	drop X_ft Y_ft LAT LONG OBJECTID 
	rename RASTERVALU chloride
	rename Well_Num sitenum
	generate year = `x'
	generate quarter = `y'
	save "Data\CL_tables\Replicate\Pajaro_CL_`x'_Q`y'_$outputdate.dta", replace
}
}

*QUARTER 2
local myyear 2001 2002 2003 2004 2005 2006 2007 2008 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020
local myquarter 2
foreach x of local myyear {
foreach y of local myquarter {
	import excel "Data\CL_tables\Replicate\CL_to_wells_`x'_Q`y'_$outputdate.xls", sheet("CL_to_wells_`x'_Q`y'_$outputdate") firstrow clear
	destring, replace
	drop X_ft Y_ft LAT LONG OBJECTID 
	rename RASTERVALU chloride
	rename Well_Num sitenum
	generate year = `x'
	generate quarter = `y'
	save "Data\CL_tables\Replicate\Pajaro_CL_`x'_Q`y'_$outputdate.dta", replace
}
}

*QUARTER 3
local myyear 2002 2003 2004 2005 2006 2007 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019
local myquarter 3
foreach x of local myyear {
foreach y of local myquarter {
	import excel "Data\CL_tables\Replicate\CL_to_wells_`x'_Q`y'_$outputdate.xls", sheet("CL_to_wells_`x'_Q`y'_$outputdate") firstrow clear
	destring, replace
	drop X_ft Y_ft LAT LONG OBJECTID 
	rename RASTERVALU chloride
	rename Well_Num sitenum
	generate year = `x'
	generate quarter = `y'
	save "Data\CL_tables\Replicate\Pajaro_CL_`x'_Q`y'_$outputdate.dta", replace
}
}

*QUARTER 4			
local myyear 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020
local myquarter 4
foreach x of local myyear {
foreach y of local myquarter {
	import excel "Data\CL_tables\Replicate\CL_to_wells_`x'_Q`y'_$outputdate.xls", sheet("CL_to_wells_`x'_Q`y'_$outputdate") firstrow clear
	destring, replace
	drop X_ft Y_ft LAT LONG OBJECTID 
	rename RASTERVALU chloride
	rename Well_Num sitenum
	generate year = `x'
	generate quarter = `y'
	save "Data\CL_tables\Replicate\Pajaro_CL_`x'_Q`y'_$outputdate.dta", replace
}
}

		

*Combine all 

local myyear 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020
local myquarter 4
foreach x of local myyear {
foreach y of local myquarter {
append using "Data\CL_tables\Replicate\Pajaro_CL_`x'_Q`y'_$outputdate.dta"
}
}

local myyear 2001 2002 2003 2004 2005 2006 2007 2008 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 
local myquarter 2
foreach x of local myyear {
foreach y of local myquarter {
append using "Data\CL_tables\Replicate\Pajaro_CL_`x'_Q`y'_$outputdate.dta"
}
}


local myyear 2002 2003 2004 2005 2006 2007 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019
local myquarter 3
foreach x of local myyear {
foreach y of local myquarter {
append using "Data\CL_tables\Replicate\Pajaro_CL_`x'_Q`y'_$outputdate.dta"
}
}

local myyear 2003 2004 2007 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 
local myquarter 1
foreach x of local myyear {
foreach y of local myquarter {
append using "Data\CL_tables\Replicate\Pajaro_CL_`x'_Q`y'_$outputdate.dta"
}
}

*********************************
duplicates drop
drop if chloride <0
drop if chloride ==.
save "Data\Pajaro_chloride_$outputdate.dta", replace


*USE CROSS WALK FILES TO ASSIGN TO PARCELS AND DWZ
merge m:1 sitenum using "Data\parcel_wells_20210526.dta"
drop _merge 
merge m:1 sitenum using "Data\inside_wells_20210526.dta",
drop _merge

sum chloride

preserve
collapse (mean) chloride, by (parcelnum inside quarter year)
rename chloride CL_parcel
duplicates drop parcelnum year quarter, force
save "Data\Pajaro_chloride_$outputdate.dta", replace
restore


preserve
collapse (mean) chloride, by (inside quarter year)
rename chloride CL_zonedate
save "Data\Pajaro_zonal_chloride_$outputdate.dta", replace
restore

preserve
*keep if quarter ==2
collapse (mean) chloride, by (inside year)
rename chloride CL_zoneyear
save "Data\Pajaro_zonalyr_chloride_$outputdate.dta", replace
restore
